package com.lxh.config.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @ClassName: SqlExecUtil
 * @Author: lxh
 * @Description: java执行sql文件
 * @Date: 2022/8/9 16:11
 */
@Component
@Slf4j
public class SqlExecUtil {

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    public void createDatabaseTable(MultipartFile file) {

        ScriptRunner scriptRunner = null;
        Connection connection = null;
        try {
            //连接导入数据库驱动
            connection = getConnection();
            Class.forName(driverClassName);
            //创建连接
            scriptRunner = new ScriptRunner(connection);
            scriptRunner.setFullLineDelimiter(false);
            scriptRunner.setSendFullScript(false);
            scriptRunner.setStopOnError(true);
            scriptRunner.runScript(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8));
            //自动提交
            scriptRunner.setAutoCommit(true);
            scriptRunner.setEscapeProcessing(false);
            //读取sql脚本所有语句一次执行 否则逐行读取
            scriptRunner.setSendFullScript(true);
            log.info("执行sql脚本文件成功");
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (Exception war1) {
                    war1.printStackTrace();
                }
            } else {
                e.printStackTrace();
            }
            e.printStackTrace();
            throw new RuntimeException("执行sql脚本文件失败");
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * @return 连接导入数据库驱动
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 执行Sql返回结果
     *
     * @param sql
     * @return
     */
    public void executeSql(String sql) {

        Map<String, Object> map = new ConcurrentHashMap<>();

        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接驱动
            connection = getConnection();

            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);

            while (resultSet.next()) {
                map.put("执行结果", resultSet.getMetaData());
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {

            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e) {
                    log.error("关闭结果集ResultSet异常！" + e.getMessage(), e);
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (Exception e) {
                    log.error("预编译SQL语句对象PreparedStatement关闭异常！" + e.getMessage(), e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    log.error("关闭连接对象Connection异常！" + e.getMessage(), e);
                }
            }
        }
    }
}

